/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package newkfp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

/**
 *
 * @author $alama
 */
@Stateless
public class StockFacade extends AbstractFacade<StockBean> {
    
    @PersistenceContext(unitName = "KFPNEWPU")
    private EntityManager em;
    
    protected EntityManager getEntityManager() {
        return em;
    }
    
    public StockFacade() {
        super(StockBean.class);
    }
    
    public List getReport(Date fromDate, Date toDate) {
        java.sql.Date from = new java.sql.Date(fromDate.getTime());
        java.sql.Date to = new java.sql.Date(toDate.getTime());
        Connection con = null;
        PreparedStatement prest;
        List<StockBean> stockList = new ArrayList<StockBean>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/kfp", "root", "root");
            try {
                String sql = "SELECT t0.idBill_Item, t1.itemName, t2.CountryName, t3.FactoryName, SUM(t0.itemAmount), AVG(t0.itemPrice), SUM(t0.totalPrice), SUM(t0.packageCount)"
                        + " FROM bill t5, bill t4, factory t3, country t2, item t1, bill_item t0"
                        + " WHERE t0.itemID = t1.idItem AND t0.factoryID = t3.idFactory AND t2.idcountry = t3.idFactory AND t4.idBill =t0.billId  AND t4.exporterID=0  AND t4.createDate BETWEEN ? AND ? AND t5.idBill = t0.billID GROUP BY t1.itemName , t2.CountryName , t3.FactoryName;";
                prest = con.prepareStatement(sql);
                prest.setDate(1, from);
                prest.setDate(2, to);
                ResultSet rs = prest.executeQuery();
                while (rs.next()) {
                    StockBean stock = new StockBean();
                    stock.setItem(rs.getString(2));
                    stock.setContry(rs.getString(3));
                    stock.setFactory(rs.getString(4));
                    stock.setAmountFromTime(rs.getString(5));
                    stock.setUnitPrice(rs.getString(6));
                    stock.setTotalPrice(rs.getString(7));
                    stock.setItemCount(rs.getString(8));
                    stockList.add(stock);
                }
                return stockList;
            } catch (SQLException s) {
                s.printStackTrace();
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
